﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using MySqlConnector;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using Simple.Services.System;
using Simple.Services.System.FileInfo;
using Simple.Services.System.FileInfo.Models;
using Simple.Services.System.MorningCheck;
using Simple.Services.System.Office;
using ZXing;
using static Rong.EasyExcel.ExcelDemo;

namespace Simple.Services
{
    public class ParentService : BusinessBaseService
    {
        private readonly SimpleDbContext _context;
        private readonly FileInfoService _fileInfoService;
        private readonly ClockingInMorningCheckService _clockingInMorningCheckService;
        private readonly ExcelOperationService _excelOperationService;




        public ParentService(SimpleDbContext context,
            ISimpleService services,
            FileInfoService fileInfoService,
            ClockingInMorningCheckService clockingInMorningCheckService,
            ExcelOperationService excelOperationService) : base(services)
        {
            _context = context;
            _fileInfoService = fileInfoService;
            _clockingInMorningCheckService = clockingInMorningCheckService;
            _excelOperationService = excelOperationService;
        }

        public async Task<List<ParentModel>> GetAsync()
        {
            var lstClasses = await OrganizationFilter(_context.Set<Parent>().AsQueryable());
            return MapperHelper.Map<List<ParentModel>>(await lstClasses.ToListAsync());
        }

        public async Task<List<ParentModel>> GetAsync(Guid ClassId)
        {
            var lstParent = await _context.Set<ChildrenParent>().Include(p => p.Parent).Include(p => p.Child).Where(p => p.Child.ClassesId == ClassId.ToString()).Select(p => p.Parent).ToListAsync();
            return MapperHelper.Map<List<ParentModel>>(lstParent);
        }

        public async Task<PageResultModel<ParentModel>> GetPageAsync(ParentPageInputModel input)
        {
            var result = new PageResultModel<ParentModel>();
            var query = await OrganizationFilter(_context.Set<Parent>().AsQueryable());

            if (!string.IsNullOrEmpty(input.ChildId))
            {
                var lstParentId = _context.Set<ChildrenParent>().Where(p => p.ChildId.ToString() == input.ChildId).Select(p => p.ParentId).ToList();
                query = query.Where(u => lstParentId.Contains(u.Id));
            }
            // 根据条件查询
            if (!string.IsNullOrEmpty(input.PhoneNumber))
            {
                query = query.Where(u => u.PhoneNumber.Contains(input.PhoneNumber));
            }
            if (!string.IsNullOrEmpty(input.Name))
            {
                var lstParentId = _context.Set<ChildrenParent>().Include(x => x.Child).Where(p => p.Child.Name.Contains(input.Name)).Select(p => p.ParentId).ToList();
                query = query.Where(u => u.Name.Contains(input.Name) || lstParentId.Contains(u.Id));
            }
            if (!string.IsNullOrEmpty(input.ClassId))
            {
                var lstParentId = _context.Set<ChildrenParent>().Include(x => x.Child).Where(p => p.Child.ClassesId == input.ClassId).Select(p => p.ParentId).ToList();
                query = query.Where(p => lstParentId.Contains(p.Id));
            }


            // 获取总数量
            result.TotalRows = await query.CountAsync();

            // 分页查询
            query = query.OrderBy(u => u.CreatedTime).Page(input.PageNo, input.PageSize);
            var lstparent = await query.ToListAsync();
            var lstParentDto = MapperHelper.Map<List<ParentModel>>(lstparent);

            if (lstParentDto.Any())
            {
                var lstChildrenParent = _context.Set<ChildrenParent>().Include(p => p.Child).Select(p => new ChildrensListDto()
                {
                    ParentId = p.ParentId,
                    ChildrenId = p.ChildId,
                    Relationship = (int)p.Relationship,
                    RelationshipStr = EnumHelper.GetDescriptionContent(p.Relationship),
                    Name = p.Child.Name,
                    StudentNumber = p.Child.StudentNumber,
                    Birthday = p.Child.Birthday,
                    Gender = p.Child.Gender,
                    HomeAddress = p.Child.HomeAddress,
                }).ToList();
                foreach (var item in lstParentDto)
                {
                    var lstParentChild = lstChildrenParent.Where(p => p.ParentId == item.Id).ToList();
                    item.ChildrenList = lstParentChild;
                    item.ChildrensName = string.Join(",", lstParentChild.Select(p => p.Name));
                    item.ChildrensNameAndRelationship = lstParentChild.Count > 1 ? string.Join(",", lstParentChild.Select(p => $"{p.Name}({p.RelationshipStr})")) : lstParentChild.FirstOrDefault()?.RelationshipStr;
                    item.Relationship = lstParentChild.FirstOrDefault()?.Relationship;
                    item.RelationshipStr = lstParentChild.FirstOrDefault()?.RelationshipStr;
                }
            }

            result.Rows = lstParentDto;

            result.SetPage(input);
            result.CountTotalPage();

            return result;
        }

        public async Task<int> AddAsync(ParentInputModel model)
        {
            var parent = await _context.Set<Parent>().FirstOrDefaultAsync(u => u.PhoneNumber == model.PhoneNumber);
            if (parent != null)
            {
                if (model.ChildId.HasValue)
                {
                    if (await _context.Set<ChildrenParent>().AnyAsync(p => p.ChildId == model.ChildId.Value && p.ParentId == parent.Id))
                    {
                        throw AppResultException.Status409Conflict("该家属与幼儿已经关联");
                    }
                    var childParent = new ChildrenParent()
                    {
                        ChildId = model.ChildId.Value,
                        Relationship = model.Relationship,
                        ParentId = parent.Id,
                    };
                    await _context.AddAsync(childParent);
                }
            }
            else
            {
                parent = MapperHelper.Map<Parent>(model);
                parent.PassWordMD5 = HashHelper.Md5("123456");
                if (_currentUser.OrganizationId.ToString() == "08dbdd12-9411-409f-8a68-4f2460cbace6")
                {
                    parent.IsOpenWechat = true;
                }

                var entityParent = await _context.AddAsync(parent);

                if (model.ChildId.HasValue)
                {
                    var childParent = new ChildrenParent()
                    {
                        ChildId = model.ChildId.Value,
                        Relationship = model.Relationship,
                        ParentId = entityParent.Entity.Id
                    };
                    await _context.AddAsync(childParent);
                }
            }
            return await _context.SaveChangesAsync();
        }

        public async Task<int> UpdateAsync(ParentInputModel model)
        {
            if (await _context.Set<Parent>().AnyAsync(u => u.Id != model.Id && u.PhoneNumber == model.PhoneNumber))
            {
                throw AppResultException.Status409Conflict("存在相同手机号码的家长");
            }
            if (!string.IsNullOrEmpty(model.MachineCardNumber) && await _context.Set<Parent>().AnyAsync(u => u.Id != model.Id && u.MachineCardNumber == model.MachineCardNumber))
            {
                throw AppResultException.Status409Conflict("存在相同考勤机卡号的家长");
            }

            var parent = await _context.Set<Parent>()
                .Where(u => model.Id == u.Id)
                .FirstOrDefaultAsync();

            if (parent == null)
            {
                throw AppResultException.Status404NotFound("找不到家长，更新失败");
            }

            MapperHelper.Map(model, parent);

            _context.Update(parent);
            int ret = await _context.SaveChangesAsync();

            if (ret == 0)
            {
                throw AppResultException.Status200OK("更新记录数为0");
            }
            if (model.ChildId.HasValue)
            {
                var childParent = await _context.Set<ChildrenParent>().FirstOrDefaultAsync(p => p.ChildId == model.ChildId && p.ParentId == model.Id);
                if (childParent != null)
                {
                    childParent.Relationship = model.Relationship;
                    _context.Update(childParent);
                    await _context.SaveChangesAsync();
                }
            }
            else
            {
                var childParent = _context.Set<ChildrenParent>().AsQueryable().Where(p => p.ParentId == model.Id);
                if (childParent != null && childParent.Any())
                {
                    await childParent.ForEachAsync(p => p.Relationship = model.Relationship);
                    _context.UpdateRange(childParent);
                    await _context.SaveChangesAsync();
                }
            }

            return ret;
        }

        public async Task<int> DeleteAsync(IEnumerable<Guid> ids)
        {
            var lstParent = await _context.Set<Parent>()
                .Where(u => ids.Contains(u.Id))
                .ToListAsync();
            _context.RemoveRange(lstParent);

            var lstRelation = await _context.Set<ChildrenParent>()
                .Where(p => ids.Contains(p.ParentId))
                .ToListAsync();
            _context.RemoveRange(lstParent);
            return await _context.SaveChangesAsync();
        }

        public async Task<int> OpenOrCloseWeChat(string phoneNumber)
        {
            var parentList = await _context.Set<Parent>().Where(p => p.PhoneNumber == phoneNumber).ToListAsync();
            if (parentList == null)
            {
                throw AppResultException.Status404NotFound("找不到家长，更新失败");
            }
            foreach (var item in parentList)
            {
                item.IsOpenWechat = !item.IsOpenWechat;
            }
            _context.UpdateRange(parentList);
            return await _context.SaveChangesAsync();
        }

        public async Task<int> BatchOpenOrCloseWeChat(IdsInputModel model)
        {
            var parent = await _context.Set<Parent>().Where(p => model.Ids.Contains(p.Id)).ToListAsync();
            if (parent == null)
            {
                throw AppResultException.Status404NotFound("找不到家长，更新失败");
            }
            foreach (var item in parent)
            {
                item.IsOpenWechat = model.IsOpenWechat;
            }
            _context.UpdateRange(parent);
            return await _context.SaveChangesAsync();
        }


        // TODO：重置密码
        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> Reset(Guid id)
        {
            var parent = await _context.Set<Parent>()
            .Where(u => u.Id == id)
            .FirstOrDefaultAsync();

            if (parent == null)
            {
                throw AppResultException.Status404NotFound("找不到家属，重置失败");
            }
            parent.PassWordMD5 = HashHelper.Md5("123456");
            _context.Update(parent);
            return await _context.SaveChangesAsync();
        }

        #region 移动端
        /// <summary>
        /// 修改个人信息
        /// </summary>
        /// <returns></returns>
        public async Task<int> UpdatePersonalInfo(UpdatePersonalInfoInput input)
        {
            var parent = await _context.Set<Parent>()
               .FirstOrDefaultAsync(p => p.Id == input.ParentId);
            if (!string.IsNullOrEmpty(input.MachineCardNumber) &&
                await _context.Set<Parent>().AnyAsync(u => u.Id != input.ParentId && u.MachineCardNumber == input.MachineCardNumber))
            {
                throw AppResultException.Status409Conflict("存在相同考勤机卡号的家长");
            }
            if (parent == null)
            {
                throw AppResultException.Status404NotFound("找不到个人信息，修改失败");
            }
            if (input.GenderType.HasValue)
            {
                parent.GenderType = input.GenderType.Value;
            }
            if (!string.IsNullOrWhiteSpace(input.Name))
            {
                parent.Name = input.Name;
            }
            if (input.ImgId.HasValue)
            {
                parent.ImgId = input.ImgId;
            }
            if (input.ChildId.HasValue && input.Relationship.HasValue)
            {
                var relation = await _context.Set<ChildrenParent>().FirstOrDefaultAsync(p => p.ChildId == input.ChildId && p.ParentId == input.ParentId);
                if (relation != null)
                {
                    relation.Relationship = input.Relationship.Value;
                }
            }
            if (string.IsNullOrEmpty(input.Workplace))
            {
                parent.Workplace = input.Workplace;
            }
            if (input.Degree.HasValue)
            {
                parent.Degree = (EnumParentDegree)Enum.ToObject(typeof(EnumParentDegree), input.Degree);
            }
            if (string.IsNullOrEmpty(input.MachineCardNumber))
            {
                parent.MachineCardNumber = input.MachineCardNumber;
            }
            if (input.IsPrincipalMember.HasValue)
            {
                parent.IsPrincipalMember = input.IsPrincipalMember;
            }
            var result = await _context.SaveChangesAsync();

            return result;
        }


        public async Task<ModelViewModel> GetPersonalInfoAsync(AppParentQuery model)
        {
            var result = new ModelViewModel();
            if (model.UserType == 1)
            {
                result = await _context.Set<SysUser>().Include(u => u.Organization).Include(u => u.Position).Where(x => x.Id == model.UserId).Select(x => new ModelViewModel
                {
                    UserId = x.Id,
                    OrgId = x.OrganizationId,
                    OrgName = x.Organization.Name,
                    PostId = x.PositionId,
                    PostName = x.Position.Name,
                    PhoneNumber = x.Phone,
                    UserName = x.UserName,
                    RealName = x.Name
                }).FirstOrDefaultAsync();
                if (result != null)
                {
                    result.Avatar = await _fileInfoService.GetUserPortrait(model.UserId.Value);
                }

            }
            else
            {
                result = await _context.Set<ChildrenParent>().Include(p => p.Parent).Where(x => x.ParentId == model.UserId).Select(x => new ModelViewModel
                {
                    UserId = x.Parent.Id,
                    Degree = x.Parent.Degree,
                    ImgId = x.Parent.ImgId,
                    Relationship = x.Relationship,
                    UserName = x.Parent.Name,
                    Workplace = x.Parent.Workplace,
                    PhoneNumber = x.Parent.PhoneNumber,
                    MachineCardNumber = x.Parent.MachineCardNumber,
                    RealName = x.Parent.Name
                }).FirstOrDefaultAsync();
            }


            return result;
        }

        public async Task<FileDownInputModel> NoImgParent()
        {
            var childrenparent = _context.Set<ChildrenParent>().AsQueryable();
            var childrens = _context.Set<Childrens>().AsQueryable();
            var parent = _context.Set<Parent>().AsQueryable();
            var sysClasses = _context.Set<SysClasses>().AsQueryable();

            string connectionString = "Server=140.246.101.8,3306;;Database=kindergarten;user id=kind;Password=asdf@123;charset=utf8mb4;";

            var exportList = new List<NoImgParentModel>();
            var resulitList = new List<NoImgParentModel>();


            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                string sqlQuery = @"SELECT a.ChildId, c.`Name` as ChidName,s.`Name` as ClassName,p.`Name` as ParentName,p.ImgId as ParentImg from childrenparent a
                                        LEFT JOIN childrens c on a.ChildId=c.Id
                                        LEFT JOIN sysclasses s on  c.ClassesId=s.Id
                                        LEFT JOIN parent p on a.ParentId=p.Id
                                        where ChildId in (select Id from childrens where OrganizationId='08dbdd12-9411-409f-8a68-4f2460cbace6' and IsDeleted=0
                                        )
                                        ORDER BY className desc";

                MySqlCommand command = new MySqlCommand(sqlQuery, connection);

                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var data = new NoImgParentModel();
                    data.ChidId = reader["ChildId"].ToString();
                    data.ChidName = reader["ChidName"].ToString();
                    data.ChidClassName = reader["ClassName"].ToString();
                    data.ParentName = reader["ParentName"].ToString();
                    data.ImgId = reader["ParentImg"].ToString();
                    exportList.Add(data);
                }

                foreach (var item in exportList.GroupBy(x => x.ChidName).ToList())
                {
                    var s = item.Where(x => string.IsNullOrEmpty(x.ImgId)).Count();
                    if (item.Where(x => string.IsNullOrEmpty(x.ImgId)).Count() == item.Count())
                    {
                        var data = new NoImgParentModel();
                        data.ChidId = item.First().ChidId;
                        data.ChidName = item.First().ChidName;
                        data.ParentName = item.First().ParentName;
                        data.ChidClassName = item.First().ChidClassName;
                        data.ImgId = item.First().ImgId;
                        resulitList.Add(data);
                    }
                }
                reader.Close();
            }
            return await _excelOperationService.ExportAsync(resulitList, ExportType.Children);

        }


        #endregion
    }
}
